﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Model;
using Qianli.DirSir.Common;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace Qianli.DirSir.DAL
{
    public class GoodsDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 显示商品信息
        /// </summary>
        /// <returns></returns>
        public List<GoodsInfo> Goodshow()
        {
            string sql = "select GoodsID,TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,ArticleNumber,Unit,CraneQuotation,Long,Wide,High,PurchasePrice,Barcode,Weight,Volume from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID";
            DataTable table = helper.ExecDataTable(sql);
            List<GoodsInfo> list = new List<GoodsInfo>();
            foreach (DataRow item in table.Rows)
            {
                GoodsInfo g = new GoodsInfo();
                g.GoodsID = Convert.ToInt32(item["GoodsID"]);
                g.TypeName = item["TypeName"].ToString();
                g.GoodsCoding = item["GoodsCoding"].ToString();
                g.GoodsName = item["GoodsName"].ToString();
                g.Remarks = item["Remarks"].ToString();
                g.StandardCost = Convert.ToInt32(item["StandardCost"]);
                g.WholesalePrice = Convert.ToInt32(item["WholesalePrice"]);

                g.ArticleNumber = item["ArticleNumber"].ToString();
                g.Unit = item["Unit"].ToString();
                g.CraneQuotation =Convert.ToInt32( item["CraneQuotation"]);
                g.Long = Convert.ToInt32(item["Long"]);
                g.Wide = Convert.ToInt32(item["Wide"]);
                g.High = Convert.ToInt32(item["High"]);
                g.PurchasePrice = Convert.ToInt32(item["PurchasePrice"]);
                g.Barcode = item["Barcode"].ToString();
                g.Weight = Convert.ToInt32(item["Weight"]);
                g.Volume = Convert.ToInt32(item["Volume"]);
                list.Add(g);
            }
            return list;
        }
        /// <summary>
        /// 显示商品分类
        /// </summary>
        /// <returns></returns>
        public List<GoodsTypeInfo> showGoodsType()
        {
            string sql = "select * from GoodsTypeInfo";
            DataTable table = helper.ExecDataTable(sql);
            List<GoodsTypeInfo> list = new List<GoodsTypeInfo>();
            foreach (DataRow item in table.Rows)
            {
                GoodsTypeInfo t = new GoodsTypeInfo();
                t.TypeID = Convert.ToInt32(item["TypeID"]);
                t.TypeName = item["TypeName"].ToString();
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="ss"></param>
        /// <returns></returns>
        public int addGoods(GoodsInfo ss)
        {
            if (ss.GoodsCoding ==null)
            {
                ss.GoodsCoding = System.Guid.NewGuid().ToString();
            }

            StringBuilder builder = new StringBuilder();
            builder.Append("insert into GoodsInfo(TypeID,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice) values(");
            builder.Append("@TypeID,@GoodsCoding,@GoodsName,@Remarks,@StandardCost,@WholesalePrice);select @@identity");

            SqlParameter[] parmeter ={ 
                new SqlParameter("@TypeID",SqlDbType.Int),
                new SqlParameter("@GoodsCoding",SqlDbType.VarChar),
                new SqlParameter("@GoodsName",SqlDbType.VarChar,30),
                new SqlParameter("@Remarks",SqlDbType.VarChar,30),
                new SqlParameter("@StandardCost",SqlDbType.VarChar,30),
                new SqlParameter("@WholesalePrice",SqlDbType.VarChar,30)
           };
            parmeter[0].Value = ss.TypeID;
            parmeter[1].Value = ss.GoodsCoding;
            parmeter[2].Value = ss.GoodsName;
            parmeter[3].Value = ss.Remarks;
            parmeter[4].Value = ss.StandardCost;
            parmeter[5].Value = ss.WholesalePrice;
            int Goodsid = Convert.ToInt32(helper.ExecScalar(builder.ToString(), parmeter));
            StringBuilder addinvent = new StringBuilder();
            addinvent.Append("insert into InventoryInformationInfo(GoodsID,StorehouseID,InventoryInformationNum) values(@GoodsID,@StorehouseID,@InventoryInformationNum)");
            SqlParameter[] inventsqlparament ={
                                         new SqlParameter("@GoodsID",SqlDbType.Int),
                                         new SqlParameter("@StorehouseID",SqlDbType.Int),
                                         new SqlParameter("@InventoryInformationNum",SqlDbType.Int),
                                     };
            inventsqlparament[0].Value = Goodsid;
            inventsqlparament[1].Value = ss.StorehouseID;
            inventsqlparament[2].Value = ss.InventoryInformationNum;
            int i = Convert.ToInt32(helper.ExecNonquery(addinvent.ToString(),inventsqlparament));
            return i;


        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int deleteGoods(int id)
        {
            string sql = "delete from GoodsInfo where GoodsID=" + id + "";
            return helper.ExecNonquery(sql);
        }

        /// <summary>
        /// 根据id获取goods表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public GoodsInfo GetGoodsByID(int id)
        {
            string sql = "select GoodsID,GoodsTypeInfo.TypeID,GoodsTypeInfo.TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID where GoodsID=@id ";
            GoodsInfo g = new GoodsInfo();

            SqlParameter[] parameter = { 
                                      new SqlParameter("@id",SqlDbType.Int,4) 
                                       };
            parameter[0].Value = id;

            DataTable d = helper.ExecDataTable(sql, parameter);
            g.GoodsID = Convert.ToInt32(d.Rows[0]["GoodsID"]);          
            g.TypeID = Convert.ToInt32(d.Rows[0]["TypeID"]);
            g.TypeName = d.Rows[0]["TypeName"].ToString();
            g.GoodsCoding = d.Rows[0]["GoodsCoding"].ToString();
            g.GoodsName = d.Rows[0]["GoodsName"].ToString();
            g.Remarks = d.Rows[0]["Remarks"].ToString();
            g.StandardCost = Convert.ToInt32(d.Rows[0]["StandardCost"]);
            g.WholesalePrice = Convert.ToInt32(d.Rows[0]["WholesalePrice"]);

            return g;
        }
        /// <summary>
        /// 修改物品信息
        /// </summary>
        /// <param name="g"></param>
        /// <returns></returns>
        public int UptGoods(GoodsInfo g)
        {
            string sql = "update GoodsInfo set TypeID= '" + g.TypeID + "',GoodsCoding=' " + g.GoodsCoding + "',GoodsName=' " + g.GoodsName + "',Remarks= '" + g.Remarks + "',StandardCost=' " + g.StandardCost + "',WholesalePrice=' " + g.WholesalePrice + "' where GoodsID=' " + g.GoodsID + "'";
            return helper.ExecNonquery(sql);

        }

        /// <summary>
        /// 根据姓名搜索物品表信息
        /// </summary>
        /// <param name="GoodsName"></param>
        /// <returns></returns>
        public List<GoodsInfo> SearchGoods(string GoodsName)
        {
            string sql = "select GoodsID,TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,ArticleNumber,Unit,CraneQuotation,Long,Wide,High,PurchasePrice,Barcode,Weight,Volume from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID where GoodsName like @GoodsName";

            SqlParameter[] parameter = { 
                 new SqlParameter("@GoodsName",SqlDbType.VarChar,30)                      
                                       };
            parameter[0].Value = "%" + GoodsName + "%";

            DataTable table = helper.ExecDataTable(sql,parameter);
            List<GoodsInfo> list = new List<GoodsInfo>();
            foreach (DataRow item in table.Rows)
            {
                GoodsInfo g = new GoodsInfo();
                g.GoodsID = Convert.ToInt32(item["GoodsID"]);
                g.TypeName = item["TypeName"].ToString();
                g.GoodsCoding = item["GoodsCoding"].ToString();
                g.GoodsName = item["GoodsName"].ToString();
                g.Remarks = item["Remarks"].ToString();
                g.StandardCost = Convert.ToInt32(item["StandardCost"]);
                g.WholesalePrice = Convert.ToInt32(item["WholesalePrice"]);

                g.ArticleNumber = item["ArticleNumber"].ToString();
                g.Unit = item["Unit"].ToString();
                g.CraneQuotation = Convert.ToInt32(item["CraneQuotation"]);
                g.Long = Convert.ToInt32(item["Long"]);
                g.Wide = Convert.ToInt32(item["Wide"]);
                g.High = Convert.ToInt32(item["High"]);
                g.PurchasePrice = Convert.ToInt32(item["PurchasePrice"]);
                g.Barcode = item["Barcode"].ToString();
                g.Weight = Convert.ToInt32(item["Weight"]);
                g.Volume = Convert.ToInt32(item["Volume"]);
                list.Add(g);
            }
            return list;
        }
        /// <summary>
        /// 根据商品编码获取goods表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<GoodsInfo> GetGoodsByGoodsCoding(string GoodsCoding)
        {
            string sql = "select GoodsID,GoodsTypeInfo.TypeID,GoodsTypeInfo.TypeName,GoodsCoding,GoodsName,Remarks,StandardCost,WholesalePrice,ArticleNumber,Unit,CraneQuotation,Long,Wide,High,PurchasePrice,Barcode,Weight,Volume from GoodsInfo join GoodsTypeInfo on GoodsTypeInfo.TypeID=GoodsInfo.TypeID where GoodsCoding=@GoodsCoding ";

            SqlParameter[] parameter = { 
                                      new SqlParameter("@GoodsCoding",SqlDbType.VarChar,40) 
                                       };
            parameter[0].Value = GoodsCoding;

            DataTable d = helper.ExecDataTable(sql, parameter);
            List<GoodsInfo> list = new List<GoodsInfo>();
            foreach (DataRow item in d.Rows)
            {
                GoodsInfo g = new GoodsInfo();
                g.GoodsID = Convert.ToInt32(item["GoodsID"]);
                g.TypeName = item["TypeName"].ToString();
                g.GoodsCoding = item["GoodsCoding"].ToString();
                g.GoodsName = item["GoodsName"].ToString();
                g.Remarks = item["Remarks"].ToString();
                g.StandardCost = Convert.ToInt32(item["StandardCost"]);
                g.WholesalePrice = Convert.ToInt32(item["WholesalePrice"]);

                g.ArticleNumber = item["ArticleNumber"].ToString();
                g.Unit = item["Unit"].ToString();
                g.CraneQuotation = Convert.ToInt32(item["CraneQuotation"]);
                g.Long = Convert.ToInt32(item["Long"]);
                g.Wide = Convert.ToInt32(item["Wide"]);
                g.High = Convert.ToInt32(item["High"]);
                g.PurchasePrice = Convert.ToInt32(item["PurchasePrice"]);
                g.Barcode = item["Barcode"].ToString();
                g.Weight = Convert.ToInt32(item["Weight"]);
                g.Volume = Convert.ToInt32(item["Volume"]);
                list.Add(g);
            }
            return list;
        }

    }
}
